SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_ordenDeMeritoAcumulacion
AS
SELECT DISTINCT 
                      TOP 100 PERCENT SUBSTRING(CO.Descripcion, 1, 100) AS Convocatoria, CO.idConvocatoria, N.NivelEnsenanza AS Nivel, IC.Nivel AS IdNivel, 
                      IC.Modalidad, m.Descripcion, CL.Descripcion AS Ciclo, IC.Ciclo AS IdCiclo, C.Descripcion AS CategoriaDeTitulo, A.Documento, 
                      A.Nombres AS NombreYApellido, ROUND(IC.Puntaje, 5) AS Puntaje, A.Domicilio AS Direccion, L2.localidad AS ALocalidad, A.Telefono AS TE, 
                      CASE WHEN N.IdNivelEnsenanza IN (2) THEN A.LegajoDeInicial WHEN N.IdNivelEnsenanza IN (3) 
                      THEN A.LegajoDePrimaria WHEN N.IdNivelEnsenanza IN (4) THEN A.LegajoDeMedia END AS Legajo, IC.SituacionRevista, 
                      CASE WHEN N.IdNivelEnsenanza IN (2) THEN 1 WHEN N.IdNivelEnsenanza IN (4) THEN 2 WHEN N.IdNivelEnsenanza = 3 AND 
                      CL.IdCiclo = 1 THEN 1 WHEN N.IdNivelEnsenanza = 3 AND CL.IdCiclo = 2 THEN 2 ELSE 3 END AS TituloDelReporte, 
                      CASE WHEN ic.cargogenerico IN (35, 320, 405, 620) AND ic.titulovalorado = 71870 THEN '##' WHEN ic.cargogenerico IN (55, 415, 330) AND 
                      ic.titulovalorado = 46480 THEN '**' ELSE '  ' END AS tit, IC.CategoriaTituloAdultos, CO.TipoConvocatoria, 
                      dbo.tb_Organizaciones.Nombre + ' - ' + tb_Localidades_1.localidad AS EscuelaDeOrigen, IC.TituloEspecifico
FROM         dbo.tb_Organizaciones INNER JOIN
                      dbo.tb_Localidades tb_Localidades_1 ON dbo.tb_Organizaciones.Localidad = tb_Localidades_1.idLocalidad RIGHT OUTER JOIN
                      dbo.tb_InscripcionesConvocatorias IC INNER JOIN
                      dbo.tb_Localidades L ON IC.Localidad = L.idLocalidad INNER JOIN
                      dbo.tb_CargosGenericos CG ON CG.idCargoGenerico = IC.CargoGenerico INNER JOIN
                      dbo.tb_AsignaturasGenericas AG ON AG.idAsignaturaGenerica = IC.AsignaturaGenerica INNER JOIN
                      dbo.tb_Agentes A ON A.idAgente = IC.Agente INNER JOIN
                      dbo.tb_Convocatorias CO ON CO.idConvocatoria = IC.Convocatoria INNER JOIN
                      dbo.tb_NivelesEnsenanza N ON N.idNivelEnsenanza = IC.Nivel INNER JOIN
                      dbo.tb_ModalidadesEnsenanza m ON m.idModalidad = IC.Modalidad INNER JOIN
                      dbo.tb_Ciclos CL ON CL.IdCiclo = IC.Ciclo ON dbo.tb_Organizaciones.idOrganizaciones = IC.Organizacion LEFT OUTER JOIN
                      dbo.tb_Localidades L2 ON A.Localidad = L2.idLocalidad LEFT OUTER JOIN
                      dbo.tb_CategoriasDeTitulo C ON C.idCategoria = IC.CategoriaDeTitulo
WHERE     (CO.idConvocatoria = 981)
ORDER BY IC.Modalidad, IC.CategoriaTituloAdultos, ROUND(IC.Puntaje, 5) DESC
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[31] 4[44] 2[8] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1 [56] 4 [18] 2))"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "tb_Organizaciones"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 228
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_Localidades_1"
            Begin Extent = 
               Top = 6
               Left = 266
               Bottom = 121
               Right = 456
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "IC"
            Begin Extent = 
               Top = 6
               Left = 494
               Bottom = 121
               Right = 698
            End
            DisplayFlags = 280
            TopColumn = 15
         End
         Begin Table = "L"
            Begin Extent = 
               Top = 126
               Left = 38
               Bottom = 241
               Right = 228
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CG"
            Begin Extent = 
               Top = 126
               Left = 266
               Bottom = 241
               Right = 456
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AG"
            Begin Extent = 
               Top = 126
               Left = 494
               Bottom = 241
               Right = 684
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "A"
            Begin Extent = 
               Top = 246
               Left = 38
               Bottom = 361
               Right = 240
            End
            DisplayFlags = 280
         ', 'USER', N'dbo', 'VIEW', N'vw_ordenDeMeritoAcumulacion', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'   TopColumn = 0
         End
         Begin Table = "CO"
            Begin Extent = 
               Top = 246
               Left = 278
               Bottom = 361
               Right = 468
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "N"
            Begin Extent = 
               Top = 246
               Left = 506
               Bottom = 346
               Right = 696
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "m"
            Begin Extent = 
               Top = 348
               Left = 506
               Bottom = 448
               Right = 696
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CL"
            Begin Extent = 
               Top = 366
               Left = 38
               Bottom = 451
               Right = 228
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L2"
            Begin Extent = 
               Top = 366
               Left = 266
               Bottom = 481
               Right = 456
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "C"
            Begin Extent = 
               Top = 450
               Left = 494
               Bottom = 565
               Right = 684
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      RowHeights = 220
      Begin ColumnWidths = 24
         Width = 284
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 2445
         Alias = 1845
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'USER', N'dbo', 'VIEW', N'vw_ordenDeMeritoAcumulacion', NULL, NULL
GO
DECLARE @xp int
SELECT @xp=2
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'USER', N'dbo', 'VIEW', N'vw_ordenDeMeritoAcumulacion', NULL, NULL
GO
